
with tmp_network as (
    select
        case when network_type = 3 then 1
             when network_type = 4 then 2
             when network_type = 5 then 3
             when network_type = 6 then 4
             when network_type = 2 then 5
             when network_type = 1 then 6
             else 0
        end as network_type
        ,code
        ,name as network_name
        ,agent_code
        ,agent_name
        ,fran_code as franchisee_code
        ,fran_name as franchisee_name
        ,center_code as transcenter_code
        ,center_name as transcenter_name
    from jms_dim.dim_network_whole_massage
)
insert overwrite table jms_dwd.dwd_tab_barscan_unloading_base_dt partition(dt)
select tmp_barscan.recordid              as record_id
     , tmp_barscan.billcode              as waybill_no
     , tmp_barscan.listcode              as list_code
     , tmp_barscan.subbillcode           as subbill_code
     , tmp_barscan.transfercode          as shipment_no
     , tmp_barscan.packagecode           as package_code
     , tmp_barscan.scantype              as scan_type
     , tmp_barscan.nextstation           as pre_station
     , tmp_barscan.destination           as destination
     , tmp_barscan.scanuser              as scan_user
     , tmp_barscan.inputsite             as scan_site
     , tmp_barscan.scantime              as scan_time
     , tmp_barscan.inputtime             as input_time
     , tmp_barscan.operatedate           as operate_date
     , tmp_barscan.send_deliver_user     as send_deliver_user
     , tmp_barscan.sendcustomer          as send_customer
     , tmp_barscan.sendsite              as send_site
     , tmp_barscan.destsite              as dest_site
     , tmp_barscan.transfercenter        as transfer_center
     , tmp_barscan.pcs                   as pcs
     , tmp_barscan.weight                as weight
     , tmp_barscan.goodstype             as goods_type
     , tmp_barscan.expresstype           as express_type
     , tmp_barscan.shifts                as shifts
     , tmp_barscan.transfer_deliver_fee  as transfer_deliver_fee
     , tmp_barscan.networkfee            as network_manage_fee
     , tmp_barscan.otherfee              as other_fee
     , tmp_barscan.checkflag             as check_flag
     , tmp_barscan.accountflag           as account_flag
     , tmp_barscan.receiptflag           as receipt_flag
     , tmp_barscan.returnflag            as return_flag
     , tmp_barscan.elescaleflag          as ele_scale_flag
     , tmp_barscan.unrecordflag          as unrecord_flag
     , tmp_barscan.writebackflag         as write_back_flag
     , tmp_barscan.lockflag              as lock_flag
     , tmp_barscan.remark                as vehicle_num
     , tmp_barscan.modifyuser            as modify_user
     , tmp_barscan.modifytime            as modify_time
     , tmp_barscan.mobile                as mobile
     , tmp_barscan.pistolid              as pistol_id
     , tmp_barscan.remark1               as remark1
     , tmp_barscan.remark2               as remark2
     , tmp_barscan.scansitecode          as scan_site_code
     , tmp_barscan.uploadtime            as upload_time
     , tmp_barscan.remark3               as remark3
     , tmp_barscan.scanusercode          as scan_user_code
     , tmp_barscan.send_deliver_usercode as send_deliver_user_code
     , tmp_barscan.nextstationcode       as pre_station_code
     , tmp_barscan.source                as source
     , tmp_barscan.remark5               as remark5
     , tmp_barscan.remark6               as remark6
     , tmp_barscan.remark4               as list_code_2
     , tmp_barscan.scansiteid            as scan_site_id
     , tmp_barscan.nextstationid         as pre_station_id
     , tmp_barscan.scanuserid            as scan_user_id
     , tmp_barscan.send_deliver_userid   as send_deliver_user_id
     , tmp_network.network_type          as network_type
     , tmp_network.agent_code            as site_agent_code
     , tmp_network.agent_name            as site_agent_name
     , tmp_network.franchisee_code       as site_franchisee_code
     , tmp_network.franchisee_name       as site_franchisee_name
     , tmp_network.transcenter_code      as site_transcenter_code
     , tmp_network.transcenter_name      as site_transcenter_name
     , tmp_network_next.network_type     as pre_network_type
     , case
           WHEN (LENGTH(tmp_barscan.BILLCODE) = 13) THEN 'GB'
           WHEN (LENGTH(tmp_barscan.BILLCODE) = 15 AND tmp_barscan.BILLCODE LIKE "55%") THEN 'BS'
           WHEN (LENGTH(tmp_barscan.BILLCODE) = 12 AND (tmp_barscan.BILLCODE LIKE "40%" OR BILLCODE LIKE "42%"))
               THEN 'BSB'
           WHEN (LENGTH(tmp_barscan.BILLCODE) = 15 AND tmp_barscan.BILLCODE LIKE "JT%") THEN 'JT'
           WHEN (LENGTH(tmp_barscan.BILLCODE) = 13 AND tmp_barscan.BILLCODE LIKE "B%") THEN 'JTB'
           else "others"
    end                                  as waybill_source
 --    , 'scantype'  as scan_type_id
     , '{{ execution_date | cst_ds }}'    as dt
from jms_ods.tab_barscan_unloading tmp_barscan
         left join tmp_network tmp_network on tmp_barscan.scansitecode = tmp_network.code
         left join tmp_network tmp_network_next on tmp_barscan.nextstationcode = tmp_network_next.code
where tmp_barscan.dt = '{{ execution_date | cst_ds }}';